# -*- coding=utf-8 -*-

import MySQLdb
from MySQLdb import escape_string
import pymongo

import sys
reload(sys)
sys.setdefaultencoding('utf-8')

mongo = pymongo.MongoClient("mongodb://175.102.18.112:27018").Drug.drug1

conn= MySQLdb.connect(
    host='175.102.18.112',
    port = 3306,
    user='root',
    passwd='new.123',
    db ='medication',
)
cur = conn.cursor()

#创建数据表
#cur.execute("create table student(id int ,name varchar(20),class varchar(30),age varchar(10))")

#插入一条数据
drugInfos = mongo.find()
for drugInfo in drugInfos:
    license_number = escape_string(drugInfo['me_pizhunwenhao'])
    old_license_number = escape_string(drugInfo['me_yuanpizhunwenhao'])
    base_code = escape_string(drugInfo['me_benweima'])
    base_code_notice = escape_string(drugInfo['me_remarks'])
    drug_name = escape_string(drugInfo['me_name'])
    drug_name_en = escape_string(drugInfo['me_englishname'])
    drug_product_factory = escape_string(drugInfo['me_shengchanqiye'])
    factory_address = escape_string(drugInfo['me_address'])
    drug_guige = escape_string(drugInfo['me_guige'])
    drug_jixing = escape_string(drugInfo['me_jixing'])
    drug_type = escape_string(drugInfo['me_type'])
    drug_guifan_jixing = escape_string(drugInfo['me_guifanjixing'])
    drug_yblb = escape_string(drugInfo['me_yblb'])
    instruction = drugInfo['instruction']
    if 'me_context' in instruction:
        drug_instruction = escape_string(str(instruction["me_context"]))
        instruction_xingzhuang = escape_string(str(instruction['me_xingzhuang']))
        instruction_chengfen = escape_string(str(instruction['me_chengfen']))
        instruction_yongfa = escape_string(str(instruction['me_yongfa']))
        instruction_shiyingzheng = escape_string(str(instruction['me_shiyingzheng']))
        instruction_fanying = escape_string(str(instruction['me_fanying']))
        instruction_jingji = escape_string(str(instruction['me_jingji']))
        instruction_zhucang = escape_string(str(instruction['me_zhucang']))
        instruction_zhuyi = escape_string(str(instruction['me_zhuyi']))
    else:
        drug_instruction = ""
        instruction_xingzhuang = ""
        instruction_chengfen = ""
        instruction_yongfa = ""
        instruction_shiyingzheng = ""
        instruction_fanying = ""
        instruction_jingji = ""
        instruction_zhucang = ""
        instruction_zhuyi = ""



    insertSQL = "insert into drug_info( license_number, old_license_number, base_code, base_code_notice, drug_name, drug_name_en, drug_product_factory,factory_address,drug_guige,drug_jixing,drug_type,drug_guifan_jixing,drug_yblb,drug_instruction,instruction_xingzhuang,instruction_chengfen,instruction_yongfa,instruction_shiyingzheng,instruction_fanying,instruction_jingji,instruction_zhucang,instruction_zhuyi) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')"% (
        license_number,old_license_number,base_code,base_code_notice,drug_name,drug_name_en,drug_product_factory,
        factory_address,drug_guige,drug_jixing,drug_type,drug_guifan_jixing,drug_yblb,drug_instruction,
        instruction_xingzhuang,instruction_chengfen,instruction_yongfa,instruction_shiyingzheng,instruction_fanying,
        instruction_jingji,instruction_zhucang,instruction_zhuyi
    )


    cur.execute(insertSQL)




#修改查询条件的数据
#cur.execute("update student set class='3 year 1 class' where name = 'Tom'")

#删除查询条件的数据
#cur.execute("delete from student where age='9'")

    conn.commit()
cur.close()
conn.close()
